﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace DAL
{
    public class BindIdDAL
    {
#pragma warning disable CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”
        private static string Conn = ConfigurationSettings.AppSettings["con"].ToString();
#pragma warning restore CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”

        public bool verifyOpenid(string openid)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@openid", openid)
            };
            string cmdText = "select * from IdBind  with(nolock) where openid = @openid";
            DataSet dataSet = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, cmdText, commandParameters);
            return dataSet != null && dataSet.Tables[0].Rows.Count > 0;
        }
        /// <summary>
        /// 水控消费
        /// </summary>
        /// <param name="machinenumber"></param>
        /// <param name="carnumber"></param>
        /// <param name="money"></param>
        /// <param name="xfjh"></param>
        /// <param name="consumption_type">00：水控消费</param>
        /// <param name="deduction_type1"></param>
        /// <param name="consumption_type1"></param>
        /// <param name="transdtTm"></param>
        /// <param name="xffs"></param>
        /// <param name="cdmc"></param>
        /// <param name="number"></param>
        /// <param name="MenuId"></param>
        /// <param name="ordernumber"></param>
        /// <returns></returns>
        public static int warteIn(string machinenumber, string carnumber, double money, string xfjh, string consumption_type, string deduction_type1, string consumption_type1, string transdtTm, string xffs, string cdmc, int number, int MenuId, string ordernumber)
        {
            ApplicationLog.Write("水控扣费金额money：" + money);
            try
            {
                SqlParameter[] sp = new SqlParameter[]
                {
                    new SqlParameter("@Machine_Number",machinenumber),
                    new SqlParameter("@cardNumber",carnumber),
                    new SqlParameter("@money",money),
                    new SqlParameter("@xfjh",xfjh),
                    new SqlParameter("@consumption_type",consumption_type),
                    new SqlParameter("@deduction_type1",deduction_type1),
                    new SqlParameter("@consumption_type1",consumption_type1),
                    new SqlParameter("@transdtTm",transdtTm),
                    new SqlParameter("@xffs",xffs),
                    new SqlParameter("@cdmc",cdmc),
                    new SqlParameter("@number",number),
                    new SqlParameter("@MenuId",MenuId),
                    new SqlParameter("@ordernumber",ordernumber)
                };
                string sql = "USP_Fac_Consumption";
                return SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, sql, sp);
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("异常warteIn：" + ex.Message);
                return -1;
            }
        }
        /// <summary>
        /// 获取当前用户出水工作状态
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public static DataTable wkstate_lssj(string carnumber)
        {
            DataTable data = new DataTable();
            try
            {
                SqlParameter[] sp = new SqlParameter[]
                {
                    new SqlParameter("@carnumber",carnumber)
                };
                string sql = "select top(1)* from lssj where rfkh=@carnumber  and xffs='二维码消费'  and xfzl='消费' and xffs <> '因错退还'  order by id desc";
                return SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("异常：wkstate_lssj" + ex.Message);
                return data;
            }

        }
        /// <summary>
        /// 更新水控状态
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public static int wkstate_update(string carnumber, int jh, int wkstate)
        {

            try
            {
                SqlParameter[] sp = new SqlParameter[]
                {
                    new SqlParameter("@carnumber",carnumber),
                    new SqlParameter("@jh",jh),
                    new SqlParameter("@wkstate",wkstate)
                };
                
                string sql = "Refundwater";
                return SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, sql, sp);
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("异常：wkstate_update" + ex.Message);
                return -1;
            }

        }


        //通过openid获取到账户，姓名，密码
        public DataSet getUserInfo3(string openid)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid)
            };

            ApplicationLog.Write("后台获取值：" + openid);
            string sqlstr = "select ryxx.rfkh,ryxm,password,openid,userno from ryxx inner join IdBind on ryxx.ryid=IdBind.ryid where IdBind.openidwechat=@openid ";
            DataSet ds = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sqlstr, sp);
            return ds;
        }

        //通过openid删除idbind表人员
        public int getUserInfo4(string openid)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@openid",openid)
            };
            string sqlstr = "delete IdBind where openidwechat=@openid ";
            var Num = SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, sqlstr, sp);
            return Num;
        }

        /// <summary>
        /// 获取系统参数设置
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public static DataTable systemset()
        {
            DataTable data = new DataTable();
            try
            {
                string sql = "select * from systemset";
                return SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sql).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("异常：systemset" + ex.Message);
                return data;
            }

        }

        /// <summary>
        /// 获取人员信息
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public static DataTable getryxx(string openid)
        {
            DataTable data = new DataTable();
            try
            {
                SqlParameter[] sp = new SqlParameter[]
                {
                    new SqlParameter("@openid", openid)
                };
                string sql = "select * from ryxx where ryid in (select ryid from IdBind where openid=@openid)";
                return SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("异常：getryxx" + ex.Message);
                return data;

            }

        }
        public DataTable verifyUser(string CardId, string UserName, string pwd, string UA)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@CardId", CardId),
                new SqlParameter("@UserName", UserName),
                new SqlParameter("@UA", UA),
                new SqlParameter("@pwd", pwd)
            };
            string cmdText = String.Empty;
            if (UA.Contains("操作员"))
            {
                cmdText = @"select * from qx with(nolock) where username=@UserName and [password]=@pwd";
            }
            else
            {
                cmdText = @"select * from ryxx with(nolock) where  (Tel = @CardId or dh = @CardId or rybh = @CardId or sfz = @CardId or rfkh = @CardId)  and (ryxm=@UserName and password = @pwd and rfzt <> 64 and rfzt <> 160 ) ";
            }
            return SqlHelper.ExecuteDataSet(Conn, CommandType.Text, cmdText, commandParameters).Tables[0];
        }

        public DataTable verifyUser_Tel(string CardId, string UserName, string CUST_ID)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@CardId", CardId),
                new SqlParameter("@UserName", UserName),
                new SqlParameter("@CUST_ID", CUST_ID)
            };
            string cmdText = @"select * from ryxx with(nolock) where  (Tel = @CardId or dh = @CardId  or rfkh = @CardId)  and ( bankcard = @CUST_ID and rfzt <> 64 and rfzt <> 160 ) ";
            
            return SqlHelper.ExecuteDataSet(Conn, CommandType.Text, cmdText, commandParameters).Tables[0];
        }

        public int getRyid(string cardid)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@cardid", cardid)
            };
            string cmdText = "select ryid from ryxx where rfkh = @cardid or rybh = @cardid";
            return int.Parse(SqlHelper.ExecuteScalar(Conn, CommandType.Text, cmdText, commandParameters).ToString());
        }

        public bool insertUser(string cardid, string name, string openid)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@cardid", cardid),
                new SqlParameter("@name", name),
                new SqlParameter("@openid", openid)
            };
            int ryid = this.getRyid(cardid);
            string cmdText = "insert into IdBind select " + ryid + " ,@cardid,@name,@openid";
            return SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, cmdText, commandParameters) > 0;
        }

        public bool delUser(string openid)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@openid", openid)
            };
            string cmdText = "delete from idbind where openid = @openid";
            return SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, cmdText, commandParameters) > 0;

        }

        #region 获取最新的地址和电话
        public static DataTable Torage(string openid)
        {
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@openid",openid)
        };
            string sql = "select top(1)[address],telnum from dbo.OrderList where Ryid in(select ryid from dbo.IdBind where openid=@openid) and [address] is not null order by OrderTime desc ";
            //     表示用于填充 System.Data.DataSet 和更新 SQL Server 数据库的一组数据命令和一个数据库连接。无法继承此类。
            DataTable dt = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }

        //获取到食堂
        public static DataTable getcanteen(string OrderId)
        {
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@OrderId",OrderId)
        };
            string sql = "select distinct canteen From OrderDetail where OrderId=@OrderId ";
            //     表示用于填充 System.Data.DataSet 和更新 SQL Server 数据库的一组数据命令和一个数据库连接。无法继承此类。
            DataTable dt = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }

        //获取到订单详情
        public static DataTable get_orderdetail(string OrderId)
        {
            SqlParameter[] sp = new SqlParameter[] {
        new SqlParameter("@OrderId",OrderId)
        };
            string sql = @" select * from (select orderdetail.*,orderlist.[state],orderlist.OrderTime,orderlist.TotalPrice,orderlist.PayTime,orderlist.payType,orderlist.address,orderlist.address1,orderlist.telnum from orderdetail 
 left join orderlist on orderdetail.orderid=orderlist.orderid) orderdetail
 left join (select * from (select shelves.*,menulist.[name],menulist.price,menulist.[image],menulist.inventories from shelves 
 right join menulist on shelves.menuid=menulist.menuid) a left join timestate on a.timestateid=timestate.sdid) b on orderdetail.onsaleid=b.onsaleid where 1=1
 and orderid=@OrderId";
            //     表示用于填充 System.Data.DataSet 和更新 SQL Server 数据库的一组数据命令和一个数据库连接。无法继承此类。
            DataTable dt = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }

        #endregion

        #region 获取绑定卡号是否绑定 1，0是未绑定 2，1是已绑定
        public static int BinDingRfkh(string Opid)
        {
            SqlDataReader rs = null;
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@Opid",Opid)
            };
            string sql = "select BinDingRfkh  from ryxx where ryid in(select ryid from dbo.IdBind where openid=@Opid)";
            rs = SqlHelper.ExecuteReader(Conn, CommandType.Text, sql, sp);
            int num = 0;
            if (rs.Read())
            {
                num = rs["BinDingRfkh"] == DBNull.Value ? 0 : Convert.ToInt32(rs["BinDingRfkh"]);
            }

            return num;
        }
        #endregion


        #region 绑定卡号
        public static int BinDingrfkhs(string rfkh, string password, string Opid)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
        new SqlParameter("@rfkh",rfkh),
        new SqlParameter("@password",password),
        new SqlParameter("@Opid",Opid)
            };
            string sql = "update ryxx set rfkh=@rfkh where [password]=@password and ryid in(select ryid from dbo.IdBind where openid=@Opid)";
            return SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, sql, sp);
        }
        #endregion

        #region 修改状态绑定 1. 0未绑定 2.1已绑定
        public static int Updaterfkh(string password, string Opid)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
        new SqlParameter("@password",password),
        new SqlParameter("@Opid",Opid)
            };
            string sql = "update ryxx set BinDingRfkh=1 where [password]=@password and ryid in (select ryid from IdBind where openid=@Opid)";
            return SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, sql, sp);
        }
        #endregion

        #region 判断卡号
        public static int BinDingkh(string rfkh)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
        new SqlParameter("@rfkh",rfkh)
            };
            string sql = "select count(*) from ryxx where rfkh=@rfkh";
            return (Int32)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sql, sp);
        }
        #endregion

        #region 绑定卡号Opid表
        public static int IdBanDin(string rfkh, string Opid)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
        new SqlParameter("@rfkh",rfkh),
        new SqlParameter("@Opid",Opid)
            };
            string sql = "update dbo.IdBind set rfkh=@rfkh where openid=@Opid";
            return SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, sql, sp);
        }
        #endregion

        #region 判断密码是否正确
        public static int judgePassword(string password, string Opid)
        {
            //防止注入式攻击
            SqlParameter[] sp = new SqlParameter[]
            {
        new SqlParameter("@password",password),
        new SqlParameter("@Opid",Opid)
            };
            //sql语句
            string sql = "select count(*) from  ryxx where [password]=@password and ryid in(select ryid from dbo.IdBind where openid=@Opid)";
            return (Int32)SqlHelper.ExecuteScalar(Conn, CommandType.Text, sql, sp);
        }
        #endregion
    }
}
